package MOLE;

import java.util.*;

/** 
 Copyright (C) 2004-2008 LiTianPing 
 ���ݷ��ʻ�����(����SQLServer)
 �û������޸������Լ���Ŀ����Ҫ��
*/
public abstract class SQL
{
	//���ݿ������ַ���(web.config������)
	//<add key="ConnectionString" value="server=127.0.0.1;database=DATABASE;uid=sa;pwd=" />		
	public static String connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
	public SQL()
	{
	}

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#region ���÷���

	public static int GetMaxID(String FieldName, String TableName)
	{
		String strsql = "select max(" + FieldName + ")+1 from " + TableName;
		Object obj = GetSingle(strsql);
		if (obj == null)
		{
			return 1;
		}
		else
		{
			return Integer.parseInt(obj.toString());
		}
	}
	public static boolean Exists(String strSql, SqlParameter... cmdParms)
	{
		Object obj = GetSingle(strSql, cmdParms);
		int cmdresult;
		if ((obj.equals(null)) || (obj.equals(System.DBNull.Value)))
		{
			cmdresult = 0;
		}
		else
		{
			cmdresult = Integer.parseInt(obj.toString());
		}
		if (cmdresult == 0)
		{
			return false;
		}
		else
		{
			return true;
		}
	}
//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#endregion

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#region  ִ�м�SQL���

	/** 
	 ִ��SQL��䣬����Ӱ��ļ�¼��
	 
	 @param SQLString SQL���
	 @return Ӱ��ļ�¼��
	*/
	public static int ExecuteSql(String SQLString)
	{
		try (SqlConnection connection = new SqlConnection(connectionString))
		{
			try (SqlCommand cmd = new SqlCommand(SQLString,connection))
			{
				try
				{
					connection.Open();
					int rows = cmd.ExecuteNonQuery();
					return rows;
				}
				catch (System.Data.SqlClient.SqlException E)
				{
					connection.Close();
					throw new RuntimeException(E.getMessage());
				}
			}
		}
	}

	/** 
	 ִ�ж���SQL��䣬ʵ�����ݿ�����
	 
	 @param SQLStringList ����SQL���		
	*/
	public static void ExecuteSqlTran(ArrayList SQLStringList)
	{
		try (SqlConnection conn = new SqlConnection(connectionString))
		{
			conn.Open();
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = conn;
			SqlTransaction tx = conn.BeginTransaction();
			cmd.Transaction = tx;
			try
			{
				for (int n = 0;n < SQLStringList.size();n++)
				{
					String strsql = SQLStringList.get(n).toString();
					if (strsql.trim().length() > 1)
					{
						cmd.CommandText = strsql;
						cmd.ExecuteNonQuery();
					}
				}
				tx.Commit();
			}
			catch (System.Data.SqlClient.SqlException E)
			{
				tx.Rollback();
				throw new RuntimeException(E.getMessage());
			}
		}
	}
	/** 
	 ִ�д�һ���洢���̲����ĵ�SQL��䡣
	 
	 @param SQLString SQL���
	 @param content ��������,����һ���ֶ��Ǹ�ʽ���ӵ����£���������ţ�����ͨ�������ʽ���
	 @return Ӱ��ļ�¼��
	*/
	public static int ExecuteSql(String SQLString, String content)
	{
		try (SqlConnection connection = new SqlConnection(connectionString))
		{
			SqlCommand cmd = new SqlCommand(SQLString,connection);
			System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
			myParameter.Value = content;
			cmd.Parameters.Add(myParameter);
			try
			{
				connection.Open();
				int rows = cmd.ExecuteNonQuery();
				return rows;
			}
			catch (System.Data.SqlClient.SqlException E)
			{
				throw new RuntimeException(E.getMessage());
			}
			finally
			{
				cmd.Dispose();
				connection.Close();
			}
		}
	}
	/** 
	 �����ݿ������ͼ���ʽ���ֶ�(������������Ƶ���һ��ʵ��)
	 
	 @param strSQL SQL���
	 @param fs ͼ���ֽ�,���ݿ���ֶ�����Ϊimage�����
	 @return Ӱ��ļ�¼��
	*/
//C# TO JAVA CONVERTER WARNING: Unsigned integer types have no direct equivalent in Java:
//ORIGINAL LINE: public static int ExecuteSqlInsertImg(string strSQL,byte[] fs)
	public static int ExecuteSqlInsertImg(String strSQL, byte[] fs)
	{
		try (SqlConnection connection = new SqlConnection(connectionString))
		{
			SqlCommand cmd = new SqlCommand(strSQL,connection);
			System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
			myParameter.Value = fs;
			cmd.Parameters.Add(myParameter);
			try
			{
				connection.Open();
				int rows = cmd.ExecuteNonQuery();
				return rows;
			}
			catch (System.Data.SqlClient.SqlException E)
			{
				throw new RuntimeException(E.getMessage());
			}
			finally
			{
				cmd.Dispose();
				connection.Close();
			}
		}
	}

	/** 
	 ִ��һ�������ѯ�����䣬���ز�ѯ�����object����
	 
	 @param SQLString �����ѯ������
	 @return ��ѯ�����object��
	*/
	public static Object GetSingle(String SQLString)
	{
		try (SqlConnection connection = new SqlConnection(connectionString))
		{
			try (SqlCommand cmd = new SqlCommand(SQLString,connection))
			{
				try
				{
					connection.Open();
					Object obj = cmd.ExecuteScalar();

					if ((obj.equals(null)) || (obj.equals(System.DBNull.Value)))
					{
						return null;
					}
					else
					{
						return obj;
					}
				}
				catch (System.Data.SqlClient.SqlException e)
				{
					connection.Close();
					throw new RuntimeException(e.getMessage());
				}
			}
		}
	}
	/** 
	 ִ�в�ѯ��䣬����SqlDataReader
	 
	 @param strSQL ��ѯ���
	 @return SqlDataReader
	*/
	public static SqlDataReader ExecuteReader(String strSQL)
	{
		SqlConnection connection = new SqlConnection(connectionString);
		SqlCommand cmd = new SqlCommand(strSQL,connection);
		try
		{
			connection.Open();
			SqlDataReader myReader = cmd.ExecuteReader();
			return myReader;
		}
		catch (System.Data.SqlClient.SqlException e)
		{
			throw new RuntimeException(e.getMessage());
		}

	}

	/** 
	 ִ�в�ѯ��䣬����DataSet
	 
	 @param SQLString ��ѯ���
	 @return DataSet
	*/
	public static DataSet Query(String SQLString)
	{
		try (SqlConnection connection = new SqlConnection(connectionString))
		{
			DataSet ds = new DataSet();
			try
			{
				connection.Open();
				SqlDataAdapter command = new SqlDataAdapter(SQLString,connection);
				command.Fill(ds,"ds");
			}
			catch (System.Data.SqlClient.SqlException ex)
			{
				throw new RuntimeException(ex.getMessage());
			}
			return ds;
		}
	}

	public static SqlDataReader GetReader(String strSQL)
	{
		return ExecuteReader(strSQL);
	}
	public static DataSet GetDataSet(String SQLString)
	{
		return Query(SQLString);
	}
	public static DataTable GetDataTable(String SQLString)
	{
		DataSet ds = Query(SQLString);
		return ds.Tables[0];
	}


//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#endregion

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#region ִ�д�������SQL���

	/** 
	 ִ��SQL��䣬����Ӱ��ļ�¼��
	 
	 @param SQLString SQL���
	 @return Ӱ��ļ�¼��
	*/
	public static int ExecuteSql(String SQLString, SqlParameter... cmdParms)
	{
		try (SqlConnection connection = new SqlConnection(connectionString))
		{
			try (SqlCommand cmd = new SqlCommand())
			{
				try
				{

					PrepareCommand(cmd, connection, null, SQLString, cmdParms);
					int rows = cmd.ExecuteNonQuery();
					cmd.Parameters.Clear();
					return rows;
				}
				catch (System.Data.SqlClient.SqlException E)
				{
					throw new RuntimeException(E.getMessage());
				}
			}
		}
	}


	/** 
	 ִ�ж���SQL��䣬ʵ�����ݿ�����
	 
	 @param SQLStringList SQL���Ĺ�ϣ���keyΪsql��䣬value�Ǹ�����SqlParameter[]��
	*/
	public static void ExecuteSqlTranByArraylist(ArrayList SQLList)
	{
		try (SqlConnection conn = new SqlConnection(connectionString))
		{
			conn.Open();
			try (SqlTransaction trans = conn.BeginTransaction())
			{
				SqlCommand cmd = new SqlCommand();
				try
				{
					//ѭ��
					for (Hashtable o : SQLList)
					{
						 for (DictionaryEntry myDE : o)
						 {
							String cmdText = myDE.Key.toString();
							SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
							PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
							int val = cmd.ExecuteNonQuery();
							cmd.Parameters.Clear();
						 }
					}
					trans.Commit();
					trans.Dispose();
					cmd.Dispose();
				}
				catch (java.lang.Exception e)
				{
					trans.Rollback();

					throw e;
				}
				conn.Close();
				conn.Dispose();
			}
		}
	}


	/** 
	 ִ�ж���SQL��䣬ʵ�����ݿ�����
	 
	 @param SQLStringList SQL���Ĺ�ϣ���keyΪsql��䣬value�Ǹ�����SqlParameter[]��
	*/
	public static void ExecuteSqlTran(Hashtable SQLStringList)
	{
		try (SqlConnection conn = new SqlConnection(connectionString))
		{
			conn.Open();
			try (SqlTransaction trans = conn.BeginTransaction())
			{
				SqlCommand cmd = new SqlCommand();
				try
				{
					//ѭ��
					for (DictionaryEntry myDE : SQLStringList)
					{
						String cmdText = myDE.Key.toString();
						SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
						PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
						int val = cmd.ExecuteNonQuery();
						cmd.Parameters.Clear();


					}
						trans.Commit();
				}
				 catch (java.lang.Exception e)
				 {
					trans.Rollback();
					throw e;
				 }
			}
		}
	}


	/** 
	 ִ��һ�������ѯ�����䣬���ز�ѯ�����object����
	 
	 @param SQLString �����ѯ������
	 @return ��ѯ�����object��
	*/
	public static Object GetSingle(String SQLString, SqlParameter... cmdParms)
	{
		try (SqlConnection connection = new SqlConnection(connectionString))
		{
			try (SqlCommand cmd = new SqlCommand())
			{
				try
				{
					PrepareCommand(cmd, connection, null, SQLString, cmdParms);
					Object obj = cmd.ExecuteScalar();
					cmd.Parameters.Clear();
					if ((obj.equals(null)) || (obj.equals(System.DBNull.Value)))
					{
						return null;
					}
					else
					{
						return obj;
					}
				}
				catch (System.Data.SqlClient.SqlException e)
				{
					throw new RuntimeException(e.getMessage());
				}
			}
		}
	}

	/** 
	 ִ�в�ѯ��䣬����SqlDataReader
	 
	 @param strSQL ��ѯ���
	 @return SqlDataReader
	*/
	public static SqlDataReader ExecuteReader(String SQLString, SqlParameter... cmdParms)
	{
		SqlConnection connection = new SqlConnection(connectionString);
		SqlCommand cmd = new SqlCommand();
		try
		{
			PrepareCommand(cmd, connection, null, SQLString, cmdParms);
			SqlDataReader myReader = cmd.ExecuteReader();
			cmd.Parameters.Clear();
			return myReader;
		}
		catch (System.Data.SqlClient.SqlException e)
		{
			throw new RuntimeException(e.getMessage());
		}

	}

	/** 
	 ִ�в�ѯ��䣬����DataSet
	 
	 @param SQLString ��ѯ���
	 @return DataSet
	*/
	public static DataSet Query(String SQLString, SqlParameter... cmdParms)
	{
		try (SqlConnection connection = new SqlConnection(connectionString))
		{
			SqlCommand cmd = new SqlCommand();
			PrepareCommand(cmd, connection, null, SQLString, cmdParms);
			try (SqlDataAdapter da = new SqlDataAdapter(cmd))
			{
				DataSet ds = new DataSet();
				try
				{
					da.Fill(ds,"ds");
					cmd.Parameters.Clear();
				}
				catch (System.Data.SqlClient.SqlException ex)
				{
					throw new RuntimeException(ex.getMessage());
				}
				return ds;
			}
		}
	}


	private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, String cmdText, SqlParameter[] cmdParms)
	{
		if (conn.State != ConnectionState.Open)
		{
			conn.Open();
		}
		cmd.Connection = conn;
		cmd.CommandText = cmdText;
		if (trans != null)
		{
			cmd.Transaction = trans;
		}
		cmd.CommandType = CommandType.Text; //cmdType;
		if (cmdParms != null)
		{
			for (SqlParameter parm : cmdParms)
			{
				cmd.Parameters.Add(parm);
			}
		}
	}

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#endregion

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#region �洢���̲���

	/** 
	 ִ�д洢����
	 
	 @param storedProcName �洢������
	 @param parameters �洢���̲���
	 @return SqlDataReader
	*/
	public static SqlDataReader RunProcedure(String storedProcName, IDataParameter[] parameters)
	{
		SqlConnection connection = new SqlConnection(connectionString);
		SqlDataReader returnReader;
		connection.Open();
		SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
		command.CommandType = CommandType.StoredProcedure;
		returnReader = command.ExecuteReader();
		return returnReader;
	}


	/** 
	 ִ�д洢����
	 
	 @param storedProcName �洢������
	 @param parameters �洢���̲���
	 @param tableName DataSet����еı���
	 @return DataSet
	*/
	public static DataSet RunProcedure(String storedProcName, IDataParameter[] parameters, String tableName)
	{
		try (SqlConnection connection = new SqlConnection(connectionString))
		{
			DataSet dataSet = new DataSet();
			connection.Open();
			SqlDataAdapter sqlDA = new SqlDataAdapter();
			sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
			sqlDA.Fill(dataSet, tableName);
			connection.Close();
			return dataSet;
		}
	}


	/** 
	 ���� SqlCommand ����(��������һ���������������һ������ֵ)
	 
	 @param connection ���ݿ�����
	 @param storedProcName �洢������
	 @param parameters �洢���̲���
	 @return SqlCommand
	*/
	private static SqlCommand BuildQueryCommand(SqlConnection connection, String storedProcName, IDataParameter[] parameters)
	{
		SqlCommand command = new SqlCommand(storedProcName, connection);
		command.CommandType = CommandType.StoredProcedure;
		for (SqlParameter parameter : parameters)
		{
			command.Parameters.Add(parameter);
		}
		return command;
	}

	/** 
	 ִ�д洢���̣�����Ӱ�������		
	 
	 @param storedProcName �洢������
	 @param parameters �洢���̲���
	 @param rowsAffected Ӱ�������
	 @return 
	*/
	public static int RunProcedure(String storedProcName, IDataParameter[] parameters, tangible.RefObject<Integer> rowsAffected)
	{
		try (SqlConnection connection = new SqlConnection(connectionString))
		{
			int result;
			connection.Open();
			SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
			rowsAffected.argValue = command.ExecuteNonQuery();
			result = (int)command.Parameters["ReturnValue"].Value;
			//Connection.Close();
			return result;
		}
	}

	/** 
	 ���� SqlCommand ����ʵ��(��������һ������ֵ)	
	 
	 @param storedProcName �洢������
	 @param parameters �洢���̲���
	 @return SqlCommand ����ʵ��
	*/
	private static SqlCommand BuildIntCommand(SqlConnection connection, String storedProcName, IDataParameter[] parameters)
	{
		SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
		command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int,4,ParameterDirection.ReturnValue, false,0,0,"",DataRowVersion.Default,null));
		return command;
	}
//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#endregion


	public static DataTable GetDataTable(String SQLString, SqlParameter[] para)
	{
		DataSet ds = Query(SQLString, para);
		return ds.Tables[0];
	}
}